﻿Imports System.Data.OleDb
Imports DTO

Public Class ChiNhanhDAO
    Public Function ThemChiNhanh(ByVal cnDTO As ChiNhanhDTO) As Integer
        Dim sql As String = "INSERT INTO ChiNhanh(TenCN, SoDTCN) values(@TenCN, @SoDTCN)"
        Dim conn As OleDbConnection = DataProvider.ConnectDB()
        Dim cmd As New OleDbCommand(sql, conn)
        Dim para As OleDbParameter
        'Dien gia tri cho @TenCN va @SoDTCN
        para = cmd.Parameters.Add("@TenCN", OleDbType.VarChar)
        para.Value = cnDTO.TenCN
        para = cmd.Parameters.Add("@SoDTCN", OleDbType.VarChar)
        para.Value = cnDTO.SoDTCN
        'Thuc thi sql
        Dim dong As Integer = cmd.ExecuteNonQuery()
        'Dong ket noi CSDL
        conn.Close()
        If dong >= 0 Then
            Return 1 'Them thanh cong
        Else
            Return 0 ' Them that bai
        End If
    End Function
    Public Function LaySoKHTheoChinh() As List(Of ChiNhanhDTO)
        Dim sql As String = "select cn.MaCN, TenCN, SoDTCN, count(kh.MaKH)"
        sql &= "from ChiNhanh cn, KhachHang kh where cn.MaCN = kh.MaCN"
        sql &= "group by cn.MaCN, TenCN, SoDTCN"
        Dim conn As OleDbConnection = DataProvider.ConnectDB()
        Dim cmd As New OleDbCommand(sql, conn)
        Dim reader As OleDbDataReader = cmd.ExecuteReader()
        Dim danhsach As New List(Of ChiNhanhDTO)
        While reader.Read()
            Dim cnDTO As New ChiNhanhDTO()
            cnDTO.MaCN = reader.GetInt32(0)
            cnDTO.TenCN = reader.GetString(1)
            cnDTO.SoDTCN = reader.GetString(2)
            cnDTO.SoKH = reader.GetInt32(3)

            danhsach.Add(cnDTO)
        End While
        conn.Close()
        Return danhsach
    End Function
    Public Function LayDSCN() As List(Of ChiNhanhDTO)
        Dim sql = "select * from ChiNhanh"
        Dim conn As OleDbConnection = DataProvider.ConnectDB()
        Dim cmd As New OleDbCommand(sql, conn)
        Dim reader As OleDbDataReader = cmd.ExecuteReader()
        Dim danhsach As New List(Of ChiNhanhDTO)
        While reader.Read()
            Dim cnDTO As New ChiNhanhDTO()
            cnDTO.MaCN = reader.GetInt32(0)
            cnDTO.TenCN = reader.GetString(1)
            cnDTO.SoDTCN = reader.GetString(2)
            danhsach.Add(cnDTO)
        End While
        conn.Close()
        Return danhsach
    End Function
End Class
